/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.insurance.cis.daos;

import com.insurance.cis.connection.CisConnection;
import com.insurance.cis.models.Campaign;
import com.insurance.cis.models.Channel;
import com.insurance.cis.models.Product;
import com.insurance.cis.models.ProductCategory;
import com.insurance.cis.models.Promotion;
import com.insurance.cis.scos.CampaignSearchCondition;
import com.insurance.cis.utils.CisConstants;
import com.insurance.cis.utils.Utils;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author TruongLQ
 */
public class CampaignDao {

    public List<Campaign> searchCampaigns(CampaignSearchCondition sco) {
        List<Campaign> campaigns = new ArrayList<>();
        ResultSet rs;
        final StringBuilder sql = new StringBuilder();

        sql.append("select distinct ");
        sql.append("camp.CampaignID as CampaignID,");
        sql.append("camp.CampaignName as CampaignName,");
        sql.append("prod.ProductID as ProductID,");
        sql.append("prod.ProductCatID as ProductCatID,");
        sql.append("prod.ProductName as ProductName,");
        sql.append("prod.ProductDesc as ProductDesc,");
        sql.append("channel.ChannelID as ChannelID,");
        sql.append("channel.ChannelDesc as ChannelDesc,");
        sql.append("promo.PromotionID as PromotionID,");
        sql.append("promo.Discount as Discount,");
        sql.append("promo.Gift as Gift,");
        sql.append("camp.EstimatedBudget as EstimatedBudget,");
        sql.append("camp.EstimatedSale as EstimatedSale,");
        sql.append("camp.ActualBudget as ActualBudget,");
        sql.append("camp.ActualSale as ActualSale,");
        sql.append("camp.StartDate as StartDate,");
        sql.append("camp.EndDate as EndDate ");

        sql.append("from Campaign camp, Product prod, Channel channel, Promotion promo ");

        sql.append("where camp.ProductID = prod.ProductID ");
        sql.append("and camp.ChannelID = channel.ChannelID ");
        sql.append("and camp.PromotionID = promo.PromotionID");
        try {
            if (sco == null || sco.isEmptyCondition()) {
                sql.append(" order by camp.CampaignID asc");
                Statement st = CisConnection.getInstance().getSqlConnection().createStatement();
                rs = st.executeQuery(sql.toString());
            } else {
                Map<Integer, Object> parameters = new HashMap<>();
                int count = 0;
                if (sco.getCampaignID()!=null&& !sco.getCampaignID().isEmpty()) {
                    sql.append(" and camp.CampaignID = ? ");
                    parameters.put(++count, sco.getCampaignID());
                }
                if (sco.getCampaignName()!=null&&!sco.getCampaignName().isEmpty()) {
                    sql.append(" and camp.CampaignName like ?");
                    parameters.put(++count, "%" + sco.getCampaignName() + "%");
                }

                if (sco.getChannelID() != null) {
                    sql.append(" and camp.ChannelID = ?");
                    parameters.put(++count, sco.getChannelID());
                }
                if (sco.getProductID()!=null&&!sco.getProductID().isEmpty()) {
                    sql.append(" and camp.ProductID = ?");
                    parameters.put(++count, sco.getProductID());
                }
                if (sco.getStartDate() != null) {
                    sql.append(" and camp.StartDate >= ?");
                    parameters.put(++count, Utils.convertDateToString(
                            sco.getStartDate(), CisConstants.DATE_YYYY_MM_DD));
                }
                if (sco.getEndDate() != null) {
                    sql.append(" and camp.EndDate <= ?");
                    parameters.put(++count, Utils.convertDateToString(
                            sco.getEndDate(), CisConstants.DATE_YYYY_MM_DD));
                }
                sql.append(" order by camp.CampaignID asc");
                PreparedStatement pst = CisConnection.getInstance()
                        .getSqlConnection().prepareStatement(sql.toString());
                for (Integer pos : parameters.keySet()) {
                    pst.setObject(pos, parameters.get(pos));
                }

                rs = pst.executeQuery();
            }
            buildCampaigns(campaigns, rs);
            return campaigns;
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
            return campaigns;
        }
    }

    private void buildCampaigns(List<Campaign> campaigns, ResultSet rs) throws SQLException {
        while (rs.next()) {
            Campaign campaign = new Campaign(
                    rs.getString("CampaignID"),
                    rs.getString("CampaignName"),
                    rs.getDouble("EstimatedBudget"),
                    rs.getDouble("EstimatedSale"),
                    rs.getDouble("ActualBudget"),
                    rs.getDouble("ActualSale"),
                    rs.getDate("StartDate"),
                    rs.getDate("EndDate"));
            campaign.setChannel(new Channel(rs.getInt("ChannelID"),
                    rs.getString("ChannelDesc")));
            campaign.setProduct(new Product(rs.getString("ProductID"),
                    rs.getString("ProductName"),
                    rs.getString("ProductDesc"),
                    new ProductCategory(rs.getInt("ProductCatID"), "")));
            campaign.setPromotion(new Promotion(rs.getInt("PromotionID"),
                    rs.getInt("Discount"), rs.getString("Gift")));
            campaigns.add(campaign);
        }
    }

    public boolean saveCampaign(Campaign campaign) {
        final String SQL = "insert into Campaign values (?,?,?,?,?,?,?,?,?,?,?)";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, campaign.getCampaignID());
            pst.setString(2, campaign.getCampaignName());
            pst.setString(3, campaign.getProduct().getId());
            pst.setInt(4, campaign.getChannel().getChannelID());
            pst.setInt(5, campaign.getPromotion().getPromotionID());
            pst.setDouble(6, campaign.getEstimatedBudget());
            pst.setDouble(7, campaign.getEstimatedSale());
            pst.setDouble(8, campaign.getActualBudget());
            pst.setDouble(9, campaign.getActualSale());
            if (campaign.getStartDate() != null) {
                pst.setDate(10, new java.sql.Date(campaign.getStartDate().getTime()));
            } else {
                pst.setDate(10, null);
            }
            if (campaign.getEndDate() != null) {
                pst.setDate(11, new java.sql.Date(campaign.getEndDate().getTime()));
            } else {
                pst.setDate(11, null);
            }
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean checkIDExist(String campaignID) {
        final String SQL = "select * from Campaign where CampaignID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, campaignID);
            ResultSet rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public boolean checkNameExist(String campaignName) {
        final String SQL = "select * from Campaign where CampaignName=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, campaignName);
            ResultSet rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public Campaign getByID(String campaignID) {
        try {
            final String SQL  = "select * from Campaign where CampaignID=?";
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, campaignID);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                Campaign campaign = new Campaign(
                        rs.getString("CampaignID"),
                        rs.getString("CampaignName"),
                        rs.getDouble("EstimatedBudget"),
                        rs.getDouble("EstimatedSale"),
                        rs.getDouble("ActualBudget"),
                        rs.getDouble("ActualSale"),
                        rs.getDate("StartDate"),
                        rs.getDate("EndDate"));
                campaign.setChannel(new Channel(rs.getInt("ChannelID"), ""));
                campaign.setProduct(new Product(rs.getString("ProductID"),"","",null));
                campaign.setPromotion(new Promotion(rs.getInt("PromotionID"),0, ""));
                return campaign;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public boolean updateCampaign(Campaign campaign) {
        final String SQL  = "select * from Campaign where CampaignID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL,ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, campaign.getCampaignID());
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                rs.updateString(2, campaign.getCampaignName());
                rs.updateString(3, campaign.getProduct().getId());
                rs.updateInt(4, campaign.getChannel().getChannelID());
                rs.updateInt(5, campaign.getPromotion().getPromotionID());
                rs.updateDouble(6, campaign.getEstimatedBudget());
                rs.updateDouble(7, campaign.getEstimatedSale());
                rs.updateDouble(8, campaign.getActualBudget());
                rs.updateDouble(9, campaign.getActualSale());
                if (campaign.getStartDate() != null) {
                    rs.updateDate(10, new java.sql.Date(campaign.getStartDate().getTime()));
                } else {
                    rs.updateDate(10, null);
                }
                if (campaign.getEndDate() != null) {
                    rs.updateDate(11, new java.sql.Date(campaign.getEndDate().getTime()));
                } else {
                    rs.updateDate(11, null);
                }
                rs.updateRow();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public boolean deleteCampaign(String campaignID) {
        final String SQL  = "delete from Campaign where CampaignID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, campaignID);
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public boolean updateEstimatedData(String campaignID, double estimatedSales, double estimatedBudget) {
        try {
            String sql = "update Campaign set EstimatedBudget=?, EstimatedSale = ? where CampaignID=?";
            PreparedStatement pst= CisConnection.getInstance().getSqlConnection().prepareStatement(sql);
            pst.setDouble(1, estimatedBudget);
            pst.setDouble(2, estimatedSales);
            pst.setString(3, campaignID);
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public boolean updateActualData(String campaignID, double actualSales, double actualBudget) {
        try {
            String sql = "update Campaign set ActualBudget=?, ActualSale = ? where CampaignID=?";
            PreparedStatement pst= CisConnection.getInstance().getSqlConnection().prepareStatement(sql);
            pst.setDouble(1, actualBudget);
            pst.setDouble(2, actualSales);
            pst.setString(3, campaignID);
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public boolean deleteCampaignsFromProductID(String productID) {
        final String SQL  = "delete from Campaign where ProductID=?";
        try {
            PreparedStatement pst = CisConnection.getInstance().getSqlConnection()
                    .prepareStatement(SQL);
            pst.setString(1, productID);
            return pst.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
}
